<?php
	/**
	 * Class that manages basic data functionalities (not much, since data is static )
	 * 
	 */
	class cc_manager{
		
		private $dbh;
		
		function __construct(){
			//create a connection
			$this->dbh = $this->connect();
			//generate camera array
		}
		
		function get_dbh(){
			return $this->dbh;
		}
		
		/**
		 * Method to connect to the database
		 * @param $db the name of the database to connect to.
		 * @return the connection handler
		 */
		function connect(){
			include('connection.php');
			$handler = mysql_connect($db_hostname, $db_username, $db_password) 
				or die("Unable to connect to MySQL");
			
			//Select Course Critique database
			$db_selected = mysql_select_db($db_db, $handler);
			if (!$db_selected) {
				die ("Can\'t use {$db_db} : " . mysql_error());
			}
			return $handler;
		}
		
		/**
		 * Method to disconnect a connection handler
		 * @param $handler the connection handler to disconnect
		 */
		function disconnect($handler){
			mysql_close($this->dbh);
		}
				

		/**
		 * Returns key->value array of all courses given
		 * a department and course number.
		 */
		function get_course($dept, $num) {
			$info = array();
			
			$dept_id = $this->get_department_from_code($dept);
			$course_id = $this->get_course_id($dept_id, $num);

			$query = "SELECT ID, CRN, Code, ProfessorID, CourseID, 
			                 TermID, Size, Percent_A, Percent_B, Percent_C, 
							 Percent_D, Percent_F, Percent_W, GPA, Survey 
							 FROM section WHERE section.CourseID={$course_id}";

			$result = mysql_query($query) or die(mysql_error());
			
			while ($row=mysql_fetch_array($result)) {
				$info[] = array("ID"=>$row['ID'], 
								"CRN"=>$row['CRN'],
								"Code"=>$row['Code'],
								"ProfessorID"=>$row['ProfessorID'],
								"CourseID"=>$row['CourseID'],
								"TermID"=>$row['TermID'],
								"Size"=>$row['Size'],
								"Percent_A"=>$row['Percent_A'],
								"Percent_B"=>$row['Percent_B'],
								"Percent_C"=>$row['Percent_C'],
								"Percent_D"=>$row['Percent_D'], 
								"Percent_F"=>$row['Percent_F'],
								"Percent_W"=>$row['Percent_W'],
								"GPA"=>$row['GPA'],
								"Survey"=>$row['Survey']);
			}
			mysql_free_result($result);
			return $info;
		}
		
		
		function get_row_from_section($sectionID) {
			
			$query = "SELECT Section.ID as SectionID,CRN,section.Code as SectionCode,ProfessorID,CourseID,TermID,Section.Size AS SizeID,Percent_A,Percent_B,Percent_C,Percent_D,Percent_F,Percent_W,GPA,Survey,course.Number AS CourseNumber,DepartmentID,department.Code AS DepartmentCode,Department.Name AS DepartmentName,FirstName,LastName,StartDate,EndDate,Semester,Year,MinimumSize,MaximumSize FROM section 
				JOIN course ON courseID = course.id 
				JOIN department ON departmentID = department.id 
				JOIN professor ON professorID = professor.id 
				JOIN term ON termID = term.id
				JOIN sectionsize ON section.size = sectionsize.id WHERE 1=1";
			$query .= (isset($sectionID)) ? " AND section.ID = \"".$sectionID."\"" : "";
			$result = mysql_query($query) or die(mysql_error());
			
			return mysql_fetch_assoc($result);
		}
		
		/**
		 * Given a department code, return the integer ID related to
		 * the department in the database
		 */
		function get_rows($dept_code, $course_num) {
			
			$query = "SELECT Section.ID as SectionID,CRN,section.Code as SectionCode,ProfessorID,CourseID,TermID,Section.Size AS SizeID,Percent_A,Percent_B,Percent_C,Percent_D,Percent_F,Percent_W,GPA,Survey,course.Number AS CourseNumber,DepartmentID,department.Code AS DepartmentCode,Department.Name AS DepartmentName,FirstName,LastName,StartDate,EndDate,Semester,Year,MinimumSize,MaximumSize FROM section 
				JOIN course ON courseID = course.id 
				JOIN department ON departmentID = department.id 
				JOIN professor ON professorID = professor.id 
				JOIN term ON termID = term.id
				JOIN sectionsize ON section.size = sectionsize.id WHERE 1=1";
			$query .= (isset($dept_code)) ? " AND department.Code = \"".$dept_code."\"" : "";
			$query .= (isset($course_num)) ? " AND course.Number = ".$course_num : "";
			//echo $query;
			
			$result = mysql_query($query) or die(mysql_error());
			$rows = array();
			while($row = mysql_fetch_assoc($result)) {
				$rows[] = $row;
			} 
			return $rows;

		}
		
		/**
		 * Get department averages from CIOS survey. 
		 * 
		 */
		function get_cios_avgs() {
			
			$query = "SELECT DepartmentID, department.Code, avg(item1mean) AS avg_item1mean, avg(item2mean) AS avg_item2mean, avg(item3mean)  AS avg_item3mean,avg(item4mean) AS avg_item4mean,avg(item5mean) AS avg_item5mean,avg(item6mean) AS avg_item6mean,avg(item7mean)  AS avg_item7mean, avg(item8mean)  AS avg_item8mean,avg(item9mean)  AS avg_item9mean, avg(item10mean) AS avg_item10mean FROM `response` JOIN section ON response.SectionID = section.ID JOIN course ON section.CourseID = course.id JOIN department ON department.id = course.DepartmentID GROUP BY DepartmentID ORDER BY department.Code ASC";
			$result = mysql_query($query) or die(mysql_error());
			$rows = array();
			while($row = mysql_fetch_assoc($result)) {
				$rows[] = $row;
			} 
			return $rows;
		}
		
		/**
		 * Given a department code, course number, semester start and end **oid**, 
		 * return the associated rows.
		 */
		function get_rows_with_date($dept_code, $course_num, $start, $end, $start_gpa, $end_gpa, $profs) {
			
			$query = "SELECT Section.ID as SectionID,CRN,section.Code as SectionCode,ProfessorID,CourseID,TermID,Section.Size AS SizeID,Percent_A,Percent_B,Percent_C,Percent_D,Percent_F,Percent_W,GPA,Survey,course.Number AS CourseNumber,DepartmentID,department.Code AS DepartmentCode,Department.Name AS DepartmentName,FirstName,LastName,StartDate,EndDate,Semester,Year,MinimumSize,MaximumSize FROM section 
				JOIN course ON courseID = course.id 
				JOIN department ON departmentID = department.id 
				JOIN professor ON professorID = professor.id 
				JOIN term ON termID = term.id
				JOIN sectionsize ON section.size = sectionsize.id WHERE 1=1";
			$query .= (isset($dept_code)) ? " AND department.Code = \"".$dept_code."\"" : "";
			$query .= (isset($course_num)) ? " AND course.Number = ".$course_num : "";
			$query .= (isset($start) && isset($end)) ? " AND term.o_ID BETWEEN ".$start." AND ".$end : "";
			$query .= (isset($start_gpa) && isset($end_gpa)) ? " AND GPA >= ".$start_gpa." AND GPA <=".$end_gpa : ""; 

			if (count($profs) > 0) {
				$query .= " AND (LastName='".mysql_real_escape_string($profs[0])."'";
				for ($i = 1; $i < count($profs); $i++) {
					$query .= " OR LastName='".mysql_real_escape_string($profs[$i])."'";
				}
				$query .= ")";
			}
			
			$result = mysql_query($query) or die(mysql_error());
			$rows = array();
			while($row = mysql_fetch_assoc($result)) {
				$rows[] = $row;
			} 
			return $rows;
		}
		
		function get_course_names() {
			$query = "SELECT department.Code,course.Number FROM (course join department on course.DepartmentID = department.ID);";
			$result = mysql_query($query) or die(mysql_error());
			$rows = array();
			while($row = mysql_fetch_assoc($result)) {
				$rows[] = $row;
			} 
			return $rows;
			
		}
		
		function get_terms() {
			$query = "SELECT id, semester, year, o_id FROM term ORDER by o_ID";
			$result = mysql_query($query) or die(mysql_error());
			$rows = array();
			while($row = mysql_fetch_assoc($result)) {
				$rows[] = $row;
			} 
			return $rows;
		}
		
		function get_department_from_code($code) {
			$query = "SELECT department.ID FROM department WHERE department.Code='{$code}'";
			$result = mysql_query($query) or die(mysql_error());
			
			$row = mysql_fetch_array($result);

			return $row['ID'];
		}
		
		/**
		 * Given a course number and departmentID, return the course number
		 * associated with the particular course.
		 */
		function get_course_id($department_id, $course_number) {
			$query = "SELECT ID FROM course WHERE DepartmentID={$department_id} AND Number={$course_number}";

			$result = mysql_query($query) or die(mysql_error());
			
			$row = mysql_fetch_array($result);
			
			return $row['ID'];
		}
		

	}
?>